Like most people I know, I write out and mail checks for bills once a week. I devised this worksheet and macro to remind me when monthly and yearly bill are due and to figure out how much money I need to pay those bills each week (Cash Flow Analysis). The sheet also compares cash required with my total monthly pay to let me know when I have extra money (almost never) or warn me when I'm in trouble. I have used this setup for three years, it has saved my bacon several times. I hope it helps you as well.
The spreadsheet uses two databases to store the monthly and yearly bills. The databases are Named Ranges which can be expanded as needed. The criteria ranges are also Named Ranges. They are set up to check whether a bill is due within the week following each Saturday of the month. If you pay your bill on a day other than Saturday, change the first number of the formula in cell (B3). Sunday is 1, Monday is 2, and so on to Saturday, the current value, which is 7.
The monthly bill database contains the day of the month the bill is due, the payee, and the amount of the payment. Be sure to hedge the due date by three days or more to cover mail delivery time. Obviously, mortgage and car payments, utility bills, and other monthly obligations go in this database.
The yearly bill database contains the month and day a bill is due, the payee, and amount of the payment. Again, hedge the due date to cover mail delays. I put everything that is not a monthly bill in this database. A yearly occurence such as birthdays, once a year bills, and so on are entered once. My auto insurance premiums are payed twice a year, so they are entered twice six months apart. Trash company bills are due quarterly, enter them four times, three months apart. This gives you the flexibility to cover just about anything.
The spreadsheet has six weeks mapped out. These cover the current month and projects one or two weeks into the following month. Unfortunately, I wasn't able to devise a method of printing both monthly and yearly bills in the print area. The search is started with Option-Command-s, monthly bills are reported in the weekly print fields. Two months of yearly bills are deposited in the print area in the lower right portion of the print area.
After the search is complete, go through and edit the various bills to include the proper amounts. I enter $0.00 in the database just to remind me when a bill is due, then type in the correct amount each month. I also copy the yearly bills from the bottom of the page to the correct week.
I run this sheet once a month just before the first Saturday and then print it out. That gives me an easy reference for the rest of the month. The files are saved in EXCEL 2.2 format, but I know for certain it works with EXCEL 3.0 and 4.0. Try it out, if you like it and find it useful, please send $7.50 to help support my daughter's college education. Thanks!
Dave Evans
2404 Westminster
St. Charles, MO 63301-4764
Address questions, comments, and suggestions for improvement to DMEVANS on America Online or the address above.
